package com.tm.mapper;

import com.tm.pojo.bo.ShopBo;
import com.tm.pojo.vo.*;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @author xiaobai
 * @create 2022-12-16 20:47
 */
@Mapper
public interface HomePageMapper {
    
    @Select("SELECT  " +
            "sa.sa_id AS 'saId', " +
            "b.business_name AS 'saBusiness', " +
            "sa.sa_img AS 'saImg', " +
            "sa.sa_business_id AS 'saBusinessId', " +
            "sa.goods_id AS 'goodsId' " +
            "FROM  " +
            "t_selected_activities sa  " +
            "LEFT JOIN  " +
            "t_business b  " +
            "on sa.sa_business_id = b.business_id " +
            "WHERE " +
            "sa.end_time > UNIX_TIMESTAMP(NOW())")
    List<SelectedActivitiesVo> selectedActivities();

    @Select("  SELECT  " +
                "    dr.daily_required_id as 'dailyRequiredId',  " +
                "    dr.goods_id as 'goodsID',  " +
                "    g.goods_banner as 'goodsBanner'  " +
            "    FROM  " +
              "    t_daily_required dr  " +
            "    LEFT JOIN  " +
              "    t_goods g  " +
            "    on  " +
             "    dr.goods_id = g.goods_id  " +
            "    WHERE  " +
            "    dr.max_age >= #{userAge} " +
            "   AND " +
                "dr.min_age <= #{userAge} " +
            "   AND " +
            "     g.goods_is_up = 0 ")
    List<DailyRequiredVo> dailyRequired(@Param("userAge") Integer userAge);

    @Select("  SELECT   " +
            "     v.video_id AS 'videoId',   " +
            "     v.video_title AS 'videoTitle',   " +
            "     v.video_author_id AS 'videoAuthorId',   " +
            "     v.video_url AS 'videoUrl',   " +
            "     v.visit_count AS 'visitCount',   " +
            "     v.thumbs_up_count AS 'thumbsUpCount',   " +
            "     u.user_name AS 'userName'   " +
            "     FROM   " +
            "     t_video v   " +
            "     LEFT JOIN   " +
            "     t_user u   " +
            "     on    " +
            "      v.video_author_id = u.user_id   " +
            "      WHERE   " +
            "      v.video_type = 0 " +
            "      ORDER BY  v.video_id DESC " +
            " limit #{startIndex},#{pageSize}")
    List<VideoVo> homeVideo(@Param("startIndex") Integer startIndex, @Param("pageSize") Integer pageSize);

    @Select("SELECT " +
            "g.goods_id AS 'goodsId', " +
            "g.goods_banner AS 'goodsBanner', " +
            "g.goods_name AS 'goodsName', " +
            "sgm.goods_sgm_price AS 'goodsSgmPrice', " +
            "sgm.sales_volume_num AS 'salesVolumeNum', " +
            "sgm.goods_stock AS 'goodsStock', " +
            "gt.type_name AS 'typeName', " +
            "g.goods_text AS 'goodsText', " +
            "b.business_name AS 'businessName' " +
            " " +
            "FROM " +
            "t_goods g " +
            "LEFT JOIN " +
            "t_specs_goods_mapping sgm " +
            "ON " +
            "g.goods_id = sgm.goods_id " +
            "LEFT JOIN " +
            "t_specs s " +
            "ON " +
            "sgm.specs_id = s.specs_id " +
            "LEFT JOIN " +
            "t_goods_type gt " +
            "ON " +
            "g.goods_type = gt.type_id " +
            "LEFT JOIN " +
            "t_business b " +
            "ON " +
            "g.goods_business_id = b.business_id " +
            "WHERE " +
            "(SELECT count(1) FROM t_goods WHERE goods_business_id = g.goods_business_id AND goods_id >g.goods_id) < 3 " +
            "AND " +
            "g.goods_is_up = 0 " +
            "GROUP BY " +
            "g.goods_id")
    List<RecommendGoodsVo> haveNiceGoods();

    @Select("SELECT  " +
                "t.text_id AS 'textId',  " +
                "t.text_img1 AS 'textImg1',  " +
                "t.text_img2 AS 'textImg2',  " +
                "t.text_img3 AS 'textImg3',  " +
                "t.text_type AS 'textTypeId',  " +
                "t.text_author_id AS 'textAuthorId',  " +
                "t.visit_count AS 'visitCount',  " +
                "t.thumbs_up_count AS 'thumbsUpCount',  " +
                "t.create_time AS 'createTime',  " +
                "t.text_title AS 'textTitle',  " +
                "ui.user_img AS 'userImg',  " +
                "t.text_content AS 'textContent', " +
                "ui.user_job AS 'userJobId'  " +
            "FROM  " +
                "t_text t  " +
            "LEFT JOIN  " +
                "t_user_info ui  " +
            "ON  " +
                "t.text_author_id = ui.user_id  " +
            "WHERE  " +
                "t.create_time > #{statTime}  " +
            "AND  " +
                "t.create_time < #{endTime} ")
    List<TextVo> beautifulInstitute(@Param("statTime") Long statTime, @Param("endTime") Long endTime);

    @Select("SELECT " +
                "t.text_id AS 'textId', " +
                "t.text_img1 AS 'textImg1', " +
                "t.text_img2 AS 'textImg2', " +
                "t.text_img3 AS 'textImg3', " +
                "t.text_type AS 'textTypeId', " +
                "t.text_author_id AS 'textAuthorId', " +
                "t.visit_count AS 'visitCount', " +
                "t.thumbs_up_count AS 'thumbsUpCount', " +
                "t.create_time AS 'createTime', " +
                "t.text_title AS 'textTitle', " +
                "ui.user_img AS 'userImg', " +
                "ui.user_job AS 'userJobId', " +
                "t.text_content AS 'textContent' " +
            "FROM " +
                "t_follow f " +
            "LEFT JOIN " +
                "t_text t " +
            "ON " +
                "f.follow_id = t.text_author_id " +
            "LEFT JOIN " +
                "t_user_info ui " +
            "ON " +
                "t.text_author_id = ui.user_id " +
            "WHERE " +
                "t.create_time > #{statTime} " +
            "AND " +
                "t.create_time < #{endTime}  " +
            "AND " +
                "f.user_id = #{userId} " +
            "ORDER BY " +
                " t.create_time DESC")
    List<TextVo> userFollowText(@Param("userId") Long userId, @Param("statTime") Long statTime, @Param("endTime") Long endTime);
    @Select("SELECT  " +
            "  t.text_id AS 'textId',  " +
            "  t.text_img1 AS 'textImg1',    " +
            "  t.text_img2 AS 'textImg2',    " +
            "  t.text_img3 AS 'textImg3',    " +
            "  t.text_type AS 'textTypeId',  " +
            "  t.text_author_id AS 'textAuthorId',  " +
            "  t.visit_count AS 'visitCount',  " +
            "  t.thumbs_up_count AS 'thumbsUpCount',  " +
            "  t.create_time AS 'createTime',  " +
            "  t.text_title AS 'textTitle',  " +
            "  ui.user_img AS 'userImg',  " +
            "  ui.user_job AS 'userJobId',  " +
            "  t.text_content AS 'textContent'  " +
            "  FROM  " +
            "    " +
            "  t_text t  " +
            "  LEFT JOIN  " +
            "  t_user_info ui  " +
            "  ON  " +
            "  t.text_author_id = ui.user_id   " +
            "  LEFT JOIN  " +
            "  t_follow f  " +
            "  ON  " +
            "t.text_author_id =  f.follow_id   " +
            "  WHERE  " +
            "  t.create_time > #{statTime}  " +
            "  AND  " +
            "  t.create_time < #{endTime}  " +
            "  AND  " +
            "  (f.user_id IS NULL or f.user_id != #{userId}  )  " +
            "  ORDER BY  " +
            "   t.create_time DESC")
    List<TextVo> unUserFollowText(@Param("userId") Long userId, @Param("statTime") Long statTime, @Param("endTime") Long endTime);

    @Select("SELECT s.shop_id as 'shopId',  " +
            "s.shop_phone as 'shopPhone',  " +
            "s.shop_name as 'shopName',  " +
            "s.shop_img as 'shopImg',  " +
            "s.shop_address_str as 'shopAddressStr',   " +
            "s.evaluation_grade as 'evaluationGrade',  " +
            "s.longitude as 'longitude',  " +
            "s.latitude as 'latitude',  " +
            "s.business_id as 'businessId',  " +
            "(SELECT count(1) FROM t_customer_presence_record cpr WHERE cpr.shop_id = s.shop_id and cpr.user_id = #{userId} ) as 'userPresence',  " +
            "(SELECT count(1) FROM t_shop_comment sc WHERE sc.shop_id = s.shop_id ) as 'commentCount'  " +
            "  FROM t_shop s  " +
            "  LEFT JOIN  " +
            "  t_business b  " +
            "  on  " +
            "  s.business_id = b.business_id  " +
            "  where   " +
            "  s.province_id = #{i}  " +
            "  AND  " +
            "  s.city_id = #{i1}")
    List<ShopBo> selectNearShop(@Param("i") int i, @Param("i1") int i1,@Param("userId") Long userId);
}
